/*
	exec sp_sales_orders_report '0','0','0','0','0','0','0','schedule',1,'12/3/2007','01/3/2008','XLREPORTS','0'
*/
alter proc sp_sales_orders_report

@customerId	VARCHAR(200),
@productId	VARCHAR(200),
@endUser	VARCHAR(200),
@endCustomer	VARCHAR(200),
@soNumner	VARCHAR(50),
@salesPerson	VARCHAR(50),
@status		VARCHAR(50),
@category	VARCHAR(50),
@orderType	INT,
@fromDate 	VARCHAR(50),
@toDate		VARCHAR(50),
@reportType	VARCHAR(50),
@defaultPrompt	VARCHAR(50)

as
begin
	CREATE TABLE #SO_TABLE (	
		[LINEITEM ID] INT,[LINEITEM NO] INT,[REQUESTED QYT] INT,[UNIT COST] FLOAT,[REQUESTED DATE] DATETIME,
		[SO BOOKING DATE] DATETIME,[SCHEDULE DATE] DATETIME,[REVISED DATE] DATETIME,
		STATUS VARCHAR(20),[ACT REQUESTED DATE] DATETIME,[PART_NUMBER] VARCHAR(50),
		[SALESORDER ID] INT,[SALESORDER NUMBER] VARCHAR(50),[CUSTOMER PO] VARCHAR(50),[SALES PERSON] VARCHAR(50),
		[END CUSTOMER] VARCHAR(50),[END USER] VARCHAR(50),[CUSTOMER NAME] VARCHAR(50),
		[ACTUAL SHIP DATE] DATETIME,[CONFIRMED DATE] DATETIME,
		[ACTUAL SHIP QTY] INT,[REMANING QTY] INT,
		ISCONSIGNMENT BIT,[ACTUAL YEAR] INT,[DATE ORDER] int, [PACKING SLIP LABEL] VARCHAR(50), 
		[INVOICE NO] VARCHAR(50),CST_STATUS VARCHAR(50),[DATE SELECTED] DATETIME )

	DECLARE @SQLSTRING	VARCHAR(4000)
	DECLARE @SQLFROMSTRING  VARCHAR(4000)	
	
	DECLARE @LINEITEMID	INT
	DECLARE @SALESORDERID	INT
	DECLARE @REQ_QTY	INT
	DECLARE @SHIP_QTY	INT
	DECLARE @REM_QTY	INT
	DECLARE @BACKLOG_QTY	INT
	DECLARE @ACTUAL_SHIP_DATE DATETIME
	DECLARE @CONFIRMED_DATE	DATETIME
	DECLARE @CST_STATUS 	VARCHAR(50)
	DECLARE @LABEL		VARCHAR(50)
	DECLARE @INVOICE_NO	VARCHAR(50)

	SET @SQLSTRING = N'
	INSERT INTO #SO_TABLE
		( [LINEITEM ID],[LINEITEM NO],[REQUESTED QYT],[UNIT COST],[REQUESTED DATE],
		  [SO BOOKING DATE],[SCHEDULE DATE],[REVISED DATE],
		  STATUS,[ACT REQUESTED DATE],[PART_NUMBER],
		  [SALESORDER ID],[SALESORDER NUMBER],[CUSTOMER PO],[SALES PERSON],
		  [END CUSTOMER],[END USER],[CUSTOMER NAME],[ACTUAL YEAR],[DATE ORDER],[DATE SELECTED] )
		 SELECT 
			COOL.LINEITEM_ID,COOL_LINE_ITEM_NO,COOL_QTY,COOL_UNIT_COST,COOL_REQUESTED_DATE,
			COOL_BOOKING_DATE,COOL_SCHEDULE_DATE,COOL_REVISED_DATE,
			COOL_STATUS,COOL_ACT_REQ_DATE,PART_NUMBER,
			SO.SalesOrder_Id,SALESORDER_NO,SO_CUSTOMER_PO_NUMBER,SO_SALES_PERSON,
			(CASE 	
				WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
	 		END),
			(CASE 
				WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
					(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
					 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END)
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
			END),C_NAME,
			(CASE	WHEN '''+ @CATEGORY +''' = ''requested'' THEN DATEPART(yyyy, COOL.COOL_REQUESTED_DATE)
				WHEN '''+ @CATEGORY +''' = ''schedule'' THEN DATEPART(yyyy, COOL.COOL_SCHEDULE_DATE)
				WHEN '''+ @CATEGORY +''' = ''revDate'' THEN DATEPART(yyyy, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE))
			END),
			(CASE	WHEN '''+ @CATEGORY +''' = ''requested'' THEN DATEPART(m, COOL.COOL_REQUESTED_DATE)
				WHEN '''+ @CATEGORY +''' = ''schedule'' THEN DATEPART(m, COOL.COOL_SCHEDULE_DATE)
				WHEN '''+ @CATEGORY +''' = ''revDate'' THEN DATEPART(m, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE))
			END),
			(CASE	WHEN '''+ @CATEGORY +''' = ''requested'' THEN COOL.COOL_REQUESTED_DATE
				WHEN '''+ @CATEGORY +''' = ''schedule'' THEN  COOL.COOL_SCHEDULE_DATE
				WHEN '''+ @CATEGORY +''' = ''revDate'' THEN ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE)
			END) '
--select *
		SET @SQLFROMSTRING = N'
		FROM 
			CUSTOMER_OPEN_ORDER_LINEITEM COOL INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID
			INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID
			INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID	'

		IF ( @customerId IS NOT NULL AND @customerId <> '0' )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND SO.CUSTOMER_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @customerId +'''))'
		END	

		IF ( @productId IS NOT NULL AND @productId <> '0' )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND COOL.PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @productId +'''))'
		END

		IF @endCustomer IS NOT NULL AND @endCustomer <> '0'
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
							 	 END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @endCustomer +'''))'
		END

		IF @endUser IS NOT NULL AND @endUser <> '0'
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
									THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
										THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
										ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
										END) 
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
									END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @endUser +'''))'
		END

		IF ( @soNumner IS NOT NULL AND @soNumner <> '0' )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND SO.SALESORDER_NO = '''+ @soNumner +''''
		END

		IF ( @salesPerson IS NOT NULL AND @salesPerson <> '0' )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND SO.SO_SALES_PERSON  IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @salesPerson +'''))'
		END

		/*IF ( @orderType IS NOT NULL AND @orderType <> 2 )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND ( ( SO_IS_CONSIGNMENT = 0 ) OR ( C_IS_Consignee = 1 and SO_IS_CONSIGNMENT = 1 ) ) AND SO.SO_IS_SAMPLE = '''+ CONVERT(VARCHAR,@orderType) +''''
		END
		ELSE IF ( @orderType IS NOT NULL AND @orderType = 2 )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND (  C_IS_Consignee = 0 and SO_IS_CONSIGNMENT = 1 ) '
		END*/

		IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE  <> 2 )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND so_is_consignment = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
		END
		ELSE IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2 )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' and so_is_consignment = 0 '
		END

		IF ( @status IS NOT NULL AND @status <> '0' )
		BEGIN
			SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND cool.COOL_Status = '''+ @status +''''
		END

		IF ( @fromDate IS NOT NULL AND @fromDate <> '0' )
		BEGIN
			IF ( @CATEGORY = 'requested' )
			BEGIN
				SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND COOL.COOL_REQUESTED_DATE >= '''+ CONVERT(VARCHAR,@fromDate, 101)+ ' 12:00AM ' +''''
			END
			ELSE IF ( @CATEGORY = 'schedule' )
			BEGIN
				SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND COOL.COOL_SCHEDULE_DATE >= '''+ CONVERT(VARCHAR,@fromDate, 101)+ ' 12:00AM ' +''''
			END
			ELSE IF ( @CATEGORY = 'revDate' )
			BEGIN
				SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) >= '''+ CONVERT(VARCHAR,@fromDate, 101)+ ' 12:00AM ' +''''
			END		
		END
		IF ( @toDate IS NOT NULL AND @toDate <> '0' )
		BEGIN
			IF ( @CATEGORY = 'requested' )
			BEGIN			
				SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND COOL.COOL_REQUESTED_DATE <= '''+ CONVERT(VARCHAR,@toDate, 101)+ ' 11:59PM ' +''''
			END
			ELSE IF ( @CATEGORY = 'schedule' )
			BEGIN			
				SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND COOL.COOL_SCHEDULE_DATE <= '''+ CONVERT(VARCHAR,@toDate, 101)+ ' 11:59PM ' +''''
			END
			ELSE IF ( @CATEGORY = 'revDate' )
			BEGIN
				SET @SQLFROMSTRING = @SQLFROMSTRING + ' AND ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) <= '''+ CONVERT(VARCHAR,@toDate, 101)+ ' 11:59PM ' +''''
			END		
		END

		EXEC (@SQLSTRING+@SQLFROMSTRING)
		--print '@SQLSTRING+@SQLFROMSTRING = '+(@SQLSTRING+@SQLFROMSTRING)

	DECLARE SOCURSOR CURSOR FOR 
		SELECT 
			[LINEITEM ID],[SALESORDER ID],[REQUESTED QYT]
		FROM 
			#SO_TABLE 
		ORDER BY 
			[SALESORDER ID],[LINEITEM ID]

	OPEN SOCURSOR
	FETCH NEXT FROM SOCURSOR INTO @LINEITEMID,@SALESORDERID,@REQ_QTY
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- NEED TO show packing slip no and its info by comma seprated,if requested.
		SELECT  
			@LABEL = LABEL,@CST_STATUS = CST_STATUS,@ACTUAL_SHIP_DATE = CST_SHIP_DATE,@INVOICE_NO = INV_NO,@CONFIRMED_DATE = CST_CONFIRMED_ON
		FROM 
			CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT,Customer_Shipping_Table CST 
		WHERE 
			CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID 
			AND CST_STATUS = 'shipped'
			AND CSLIT.LineItem_ID = @LINEITEMID

		SELECT  
			@SHIP_QTY = sum(CSLIT_QTY_SHIPPED)
		FROM 
			CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT,Customer_Shipping_Table CST 
		WHERE 
			CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID 
			AND CST_STATUS = 'shipped'
			AND CSLIT.LineItem_ID = @LINEITEMID 

			SET @REM_QTY = @REQ_QTY - ISNULL(@SHIP_QTY,0)

		if @REM_QTY < 0
		begin
			set @REM_QTY = 0
		end

		/*IF @CST_STATUS = 'SHIPPED'
		BEGIN
			SET @REM_QTY = @REQ_QTY - ISNULL(@SHIP_QTY,0)
		END
		ELSE
		BEGIN
			SET @BACKLOG_QTY = @REQ_QTY - ISNULL(@SHIP_QTY,0)
			SET @REM_QTY = @REQ_QTY - ISNULL(@SHIP_QTY,0)
		END*/
 
		UPDATE #SO_TABLE 
				SET [ACTUAL SHIP DATE] = @ACTUAL_SHIP_DATE,[CONFIRMED DATE] = @CONFIRMED_DATE,
				[ACTUAL SHIP QTY] = @SHIP_QTY,[REMANING QTY] = @REM_QTY,
				[PACKING SLIP LABEL] =  @LABEL, [INVOICE NO] = @INVOICE_NO,CST_STATUS = @CST_STATUS
		WHERE [LINEITEM ID] = @LINEITEMID

		SET @SHIP_QTY = NULL
		SET @ACTUAL_SHIP_DATE = NULL
		SET @CONFIRMED_DATE = NULL
		SET @BACKLOG_QTY = NULL
		SET @LABEL = NULL
		SET @INVOICE_NO = NULL
		SET @CST_STATUS = NULL
		--SET @BACKLOG_QTY = NULL
		SET @REM_QTY = NULL
		
	FETCH NEXT FROM SOCURSOR INTO @LINEITEMID,@SALESORDERID,@REQ_QTY
	END
	CLOSE SOCURSOR
	DEALLOCATE SOCURSOR

	IF @reportType = 'XLREPORT'
	BEGIN
		SELECT 
			[CUSTOMER NAME],[END CUSTOMER],[END USER],[SALESORDER NUMBER],[LINEITEM NO],
			[CUSTOMER PO],[PART_NUMBER],[SALES PERSON],[SO BOOKING DATE],[REQUESTED DATE],
			[SCHEDULE DATE],[REVISED DATE],[ACTUAL SHIP DATE],[CONFIRMED DATE],
			[REQUESTED QYT],[ACTUAL SHIP QTY],[REMANING QTY],[UNIT COST],STATUS
		FROM 
			#SO_TABLE
		ORDER BY [ACTUAL YEAR] ,[DATE ORDER],[DATE SELECTED]
	END
	ELSE
	BEGIN
		SELECT 
			[LINEITEM ID],[LINEITEM NO],[REQUESTED QYT],[UNIT COST],[REQUESTED DATE],
			[SO BOOKING DATE],[SCHEDULE DATE],[REVISED DATE],
			STATUS,[ACT REQUESTED DATE],[PART_NUMBER],
			[SALESORDER ID],[SALESORDER NUMBER],[CUSTOMER PO],[SALES PERSON],
			[END CUSTOMER],[END USER],[CUSTOMER NAME],[ACTUAL YEAR],[DATE ORDER]
			[ACTUAL SHIP DATE],[CONFIRMED DATE],[ACTUAL SHIP QTY],[REMANING QTY] ,
			[ACTUAL YEAR],[DATE ORDER],ISCONSIGNMENT,[PACKING SLIP LABEL],[INVOICE NO],[DATE SELECTED]
		FROM 
			#SO_TABLE
		ORDER BY [ACTUAL YEAR] ,[DATE ORDER],[DATE SELECTED]
	END
end